package com.example.covid19.util;

import com.example.covid19.common.lang.Result;
import com.example.covid19.util.ExcelReaderException;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.FileMagic;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.springframework.util.FileCopyUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.util.*;

/**
 * Excel文件读取工具类
 * @author 高新
 * @date 2022年11月25日11点33分
 */
@Slf4j
public class ExcelReader {

    /**
     * 获取工作簿
     * @param inputStream excel文件的输入流
     * @param fileMagic 文件类型魔法值
     * @return 结果
     * @throws IOException 文件或者流的IO异常
     * @throws ExcelReaderException excel读取异常
     */
    private static Workbook getWorkbook(InputStream inputStream, FileMagic fileMagic)
            throws IOException, ExcelReaderException {
        if (fileMagic == FileMagic.OLE2) {
            return new HSSFWorkbook(inputStream);
        } else if (fileMagic == FileMagic.OOXML) {
            return new XSSFWorkbook(inputStream);
        } else {
            throw new ExcelReaderException("文件类型不正确");
        }
    }

    /**
     * 转换excel文件
     * @param workbook 工作簿对象
     * @param c 数据类
     * @return 结果
     * @throws ExcelReaderException excel读取异常
     */
    private static Map<String, List<Object>> parseExcel(Workbook workbook, Class<?> c) throws ExcelReaderException {
        List<Object> objects = new ArrayList<>();
        List<Object> failList = new ArrayList<>();
        int sheetsNumber = workbook.getNumberOfSheets();
        for (int sheetNumber = 0; sheetNumber < sheetsNumber; sheetNumber++) {
            Sheet sheet = workbook.getSheetAt(sheetNumber);
            if (sheet == null) {
                continue;
            }
            int fstRowNumber = sheet.getFirstRowNum();
            Row fstRow = sheet.getRow(fstRowNumber);
            if (fstRow == null) {
                log.error(sheet.getSheetName() + " : 未找到表头");
                throw new ExcelReaderException(sheet.getSheetName() + "未找到表头行");
            }
            int fieldNumber = fstRowNumber + 1;
            Row fieldRow = sheet.getRow(fieldNumber);
            if (fieldRow == null) {
                log.error(sheet.getSheetName() + " : 未找到字段行");
                throw new ExcelReaderException(sheet.getSheetName() + "未找到字段行");
            }
            int rowStart = fstRowNumber + 2;
            int rowEnd = sheet.getPhysicalNumberOfRows();
            for (int rowNumber = rowStart; rowNumber < rowEnd; rowNumber++) {
                Row row = sheet.getRow(rowNumber);
                if (row == null) {
                    continue;
                }
                Object o = convertRowToData(row, c, fieldRow);
                if (o == null) {
                    failList.add(row);
                    log.error("第" + rowNumber + "行，数据不合法");
                    continue;
                }
                objects.add(o);
            }
        }
        Map<String, List<Object>> result = new HashMap<>(2);
        result.put("success", objects);
        result.put("error", failList);
        return result;
    }

    /**
     * 转换单元格的内容的值
     * @param cell 单元格对象
     * @return 结果
     */
    private static Object convertCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        switch (cell.getCellType()) {
            case NUMERIC:
                return new DecimalFormat("0").format(cell.getNumericCellValue());
            case STRING:
                return cell.getStringCellValue();
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case FORMULA:
                return cell.getCellFormula();
            default:
                return null;
        }
    }

    /**
     * 获取数据类的Set方法
     * @param c 数据类
     * @param field 字段名称
     * @param paramTypes 方法参数类型
     * @return 结果
     */
    private static Method getSetMethod(Class<?> c, String field, Class<?>[] paramTypes) {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("set");
        stringBuilder.append(field.substring(0, 1).toUpperCase());
        stringBuilder.append(field.substring(1));
        try {
            paramTypes[0] = c.getDeclaredField(field).getType();
            return c.getDeclaredMethod(stringBuilder.toString(), paramTypes);
        } catch (NoSuchMethodException | NoSuchFieldException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 转换行到数据对象
     * @param row 行对象
     * @param c 数据类
     * @param fieldRow 字段行
     * @return 结果
     */
    private static Object convertRowToData(Row row, Class<?> c, Row fieldRow) {
        try {
            int fN = fieldRow.getPhysicalNumberOfCells();
            String[] fName = new String[fN];
            Class<?>[] paramTypes = new Class[fN];
            for (int i = 0; i < fN; i++) {
                fName[i] = fieldRow.getCell(i).getStringCellValue();
                paramTypes[i] = c.getDeclaredField(fName[i]).getType();
            }
            int cellNumber = 0;
            Object[] objects = new Object[fN];
            for (int i = 0; i < fN; i++) {
                objects[i] = convertCellValue(row.getCell(cellNumber++));
            }
            Object o = c.getConstructor().newInstance();
            for (int i = 0; i < fN; i++) {
                Method method = getSetMethod(c, fName[i], new Class[] { paramTypes[i] });
                if (method == null) {
                    throw new NullPointerException();
                }
                if (paramTypes[i].equals(Integer.class)) {
                    objects[i] = Integer.parseInt(String.valueOf(objects[i]));
                } else {
                    objects[i] = paramTypes[i].cast(objects[i]);
                }
                method.invoke(o, objects[i]);
            }
            return o;
        } catch (IllegalAccessException |
                 InvocationTargetException |
                 NoSuchMethodException |
                 InstantiationException |
                 NoSuchFieldException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 读取excel文件
     * @param fileName 文件名
     * @param c 数据类
     * @return 结果
     * @throws ExcelReaderException excel读取错误异常
     */
    private static Map<String, List<Object>> readExcel(String fileName, Class<?> c) throws ExcelReaderException {
        Workbook workbook = null;
        FileInputStream inputStream = null;
        try {
            File file = new File(fileName);
            if (!file.exists()) {
                log.error("文件不存在。");
                throw new ExcelReaderException("文件上传异常");
            }
            inputStream = new FileInputStream(file);
            BufferedInputStream bufferedInputStream = new BufferedInputStream(inputStream);
            FileMagic fileMagic = FileMagic.valueOf(bufferedInputStream);
            workbook = getWorkbook(bufferedInputStream, fileMagic);
            return parseExcel(workbook, c);
        } catch (IOException | ExcelReaderException e) {
            log.error("解析Excel失败，文件名：" + fileName + " 错误信息：" + e.getMessage());
            e.printStackTrace();
            throw new ExcelReaderException("解析Excel失败，" + " 错误信息：" +
                    (e instanceof ExcelReaderException ? ((ExcelReaderException) e).getMsg() : e.getMessage()));
        } finally {
            try {
                if (workbook != null) {
                    workbook.close();
                }
                if (inputStream != null) {
                    inputStream.close();
                }
            } catch (Exception e) {
                log.error("数据流关闭出错");
                e.printStackTrace();
            }
        }
    }

    /**
     * 上传excel文件
     * @param multipartFile 文件对象
     * @param c 数据类
     * @return 结果
     */
    public static Result  upload(MultipartFile multipartFile, Class<?> c) {
        String path = "temp/";
        File dir = new File(path);
        if (!dir.exists()) {
            boolean create = dir.mkdir();
            if (!create) {
                return Result.fail(500, "文件创建失败", null);
            }
        }
        String fName = System.currentTimeMillis() + "_" + UUID.randomUUID();
        File file = new File(path, fName);
        try {
            FileCopyUtils.copy(multipartFile.getBytes(), file);
            return Result.success(ExcelReader.readExcel(path + fName, c));
        } catch (IOException | ExcelReaderException e) {
            e.printStackTrace();
            return Result.fail(500,
                    e instanceof ExcelReaderException
                            ? ((ExcelReaderException) e).getMsg() : "文件写入异常", null);
        } finally {
            File del = new File(path + fName);
            if (del.exists()) {
                log.info("Delete file " + path + fName + ": " + del.delete());
            }
        }
    }
}
